﻿Imports System.Data.SqlClient
Imports DTO

Public Class NguoiDungDAO

    Shared Function layDSNguoiDung() As DataTable
        Return CKetNoi.GetDataTableByStoreProc("usp_LayDSND")
    End Function

    Shared Function themNguoiDung(nd As NguoiDungDTO) As Integer
        Dim paras As New List(Of SqlParameter)
        Dim para As SqlParameter

        para = New SqlParameter("@MaNhanVien", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MaNhanVien.Replace(" ","")
        paras.Add(para)

        para = New SqlParameter("@TaiKhoan", SqlDbType.NChar, 20)
        para.Direction = ParameterDirection.Input
        para.Value = nd.TaiKhoan.Replace(" ", "")
        paras.Add(para)

        para = New SqlParameter("@MatKhau", SqlDbType.NChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MatKhau
        paras.Add(para)

        para = New SqlParameter("@DienGiai", SqlDbType.NText)
        para.Direction = ParameterDirection.Input
        para.Value = nd.DienGiai
        paras.Add(para)

        para = New SqlParameter("@MaTrangThai", SqlDbType.Bit)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MaTrangThai
        paras.Add(para)

        para = New SqlParameter("@MaNhom", SqlDbType.VarChar, 6)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MaNhom
        paras.Add(para)

        Return CKetNoi.ExecQueryStoreProc("usp_ThemND", paras)
    End Function

    Shared Function kiemTraTrung(tenTK As String) As Boolean
        Dim con As SqlConnection = CKetNoi.Connect
        con.Open()
        Dim cmd As New SqlCommand
        cmd.CommandText = "usp_ktTrung"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con

        Dim para As SqlParameter
        para = New SqlParameter("@TaiKhoan", SqlDbType.VarChar, 20)
        para.Direction = ParameterDirection.Input
        para.Value = tenTK
        cmd.Parameters.Add(para)

        para = New SqlParameter("@kq", SqlDbType.Bit)
        para.Direction = ParameterDirection.Output
        cmd.Parameters.Add(para)

        Dim kq As Boolean
        cmd.ExecuteNonQuery()
        kq = cmd.Parameters("@kq").Value
        con.Close()
        Return kq
    End Function

    Shared Function layTenQuyenCuaND(maND As String) As String
        dim tenQuyen As String=""
        Dim con As SqlConnection = CKetNoi.Connect
        con.Open()
        Dim cmd As New SqlCommand
        cmd.CommandText = "usp_layTenNhomBangMaND"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = con

        Dim para As SqlParameter
        para = New SqlParameter("@MaND", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Input
        para.Value = maND
        cmd.Parameters.Add(para)
        Dim reader As SqlDataReader = cmd.ExecuteReader
        If reader.Read Then
            tenQuyen = reader("TenNhom")
        End If
        con.Close
        Return tenQuyen
    End Function

    Shared Function layThongTinNDBangTaiKhoan(taiKhoan As String) As NguoiDungDTO
        Dim nd As New NguoiDungDTO
        Dim cn As SqlConnection = CKetNoi.Connect
        cn.Open
        Dim cmd As New SqlCommand
        cmd.Connection = cn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "usp_LayTTNguoiDungBangTaiKhoan"

        Dim para As New SqlParameter("@TaiKhoan", SqlDbType.NChar, 20)
        para.Direction = ParameterDirection.Input
        para.Value = taiKhoan
        cmd.Parameters.Add(para)

        Dim reader As SqlDataReader = cmd.ExecuteReader
        If reader.Read Then
            nd.MaNguoiDung = reader("MaNguoiDung")
            nd.MaNhanVien = reader("MaNhanVien")
            nd.TaiKhoan = reader("TaiKhoan")
            nd.DienGiai = reader("DienGiai")
            nd.MaTrangThai = reader("MaTrangThai")
            nd.MaNhom = reader("MaNhom")
        End If
        cn.Close
        Return nd
    End Function

    Shared Function capNhatNguoiDung(nd As NguoiDungDTO) As Integer
        Dim paras As New List(Of SqlParameter)
        Dim para As SqlParameter

        para = New SqlParameter("@MaNhanVien", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MaNhanVien.Replace(" ", "")
        paras.Add(para)

        para = New SqlParameter("@MaNguoiDung", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MaNguoiDung.Replace(" ", "")
        paras.Add(para)

        para = New SqlParameter("@TaiKhoan", SqlDbType.NChar, 20)
        para.Direction = ParameterDirection.Input
        para.Value = nd.TaiKhoan.Replace(" ", "")
        paras.Add(para)

        para = New SqlParameter("@MatKhau", SqlDbType.NChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MatKhau
        paras.Add(para)

        para = New SqlParameter("@DienGiai", SqlDbType.NText)
        para.Direction = ParameterDirection.Input
        para.Value = nd.DienGiai
        paras.Add(para)

        para = New SqlParameter("@MaTrangThai", SqlDbType.Bit)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MaTrangThai
        paras.Add(para)

        para = New SqlParameter("@MaNhom", SqlDbType.VarChar, 6)
        para.Direction = ParameterDirection.Input
        para.Value = nd.MaNhom
        paras.Add(para)

        Return CKetNoi.ExecQueryStoreProc("usp_CapNhatND", paras)
    End Function

    Shared Function doiMatKhau(mk As String, maND As String) As Integer
        Dim listPara As New List(Of SqlParameter)
        Dim para As SqlParameter

        para = New SqlParameter("MatKhau", SqlDbType.NChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = mk
        listPara.Add(para)

        para = New SqlParameter("MaND", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Input
        para.Value = maND
        listPara.Add(para)

        Return CKetNoi.ExecQueryStoreProc("usp_DoiMatKhau", listPara)
    End Function

    Shared Function ktMatKhau(mk As String, maND As String) As Integer
        Dim listPara As New List(Of SqlParameter)
        Dim para As SqlParameter

        para = New SqlParameter("MatKhau", SqlDbType.NChar, 50)
        para.Direction = ParameterDirection.Input
        para.Value = mk
        listPara.Add(para)

        para = New SqlParameter("MaND", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Input
        para.Value = maND
        listPara.Add(para)

        para = New SqlParameter("KQ", SqlDbType.Int)
        para.Direction = ParameterDirection.Output
        listPara.Add(para)
        Return CKetNoi.ExecQueryStoreProcReturnValueOfStore("usp_KiemTraMK", listPara, "KQ")
    End Function

End Class
